package gx.util;

import gx.model.DbProperties;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class DBUtil {
	
	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	
	private static final String DRIVER = "com.mysql.jdbc.Driver";   
	private static final String URL = "jdbc:mysql://localhost:3306/world";   
	private static final String USERNAME = "root";
	private static final String PASSWORD = "123456"; 
	
	private static String classpath = "/DB.properties";

	public Connection getConnection() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException{
		DbProperties dp=new DbProperties();
		dp.setFilePath(classpath);
		Connection conn = null;
		Class.forName(dp.getClassName()).newInstance();
		String url = dp.getUrl();
		String user = dp.getUsername();
		String password = dp.getPassword();
		conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
	
/*	 // 数据库连接   
	public synchronized Connection getConnection() {   
	try {   
	            Class.forName(DRIVER);   
	            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);   
	        } catch (ClassNotFoundException e) {   
	            e.printStackTrace();   
	           return null;   
	       } catch (SQLException e) {   
	            e.printStackTrace();   
	           return null;   
	       }   
	        return conn;   
	   } */  

	/**
	 * 获取符合输入条件的数据
	 * @param conn
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 */
	public String searchSuggest(String param) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
		//String sql = "SELECT C.NAME FROM MM_NETWORKTYPE C WHERE C.NAME LIKE '" + param + "%' ORDER BY C.NAME";
		String sql = "SELECT C.NAME FROM COUNTRY C WHERE C.NAME LIKE ' " + param +"%'";
		String name = "";
		String str = "";
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				name = rs.getString("name");
				str += name + "#";
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return "";
		}
		return str;
	}


}
